﻿<?php

/**
 * ContactForm class.
 * ContactForm is the data structure for keeping
 * contact form data. It is used by the 'contact' action of 'SiteController'.
 */
class Statistic extends ActiveRecord
{
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}
	
	public function tableName()
	{
		return 'vh_st_life_daily';
	}
	
	public function rules()
	{
		return array(
		);
	}
	
    public function stNewAndScamSiteDaily()
	{
		$query = 'TRUNCATE vh_st_life_daily';		
		$this -> executeNone($query);
	
		$query = '
		   REPLACE vh_st_life_daily(newCount, scamCount, date)
			SELECT SUM(newCount) as newCount, SUM(scamCount) as scamCount, date
			FROM(
				SELECT count(*) as newCount, 0 as scamCount, date(startDate) as date
				FROM vh_hyip	
				GROUP BY date(startDate)
				UNION ALL
				SELECT 0 as newCount, count(*) as scamCount, date(endDate) as date
				FROM vh_hyip
				GROUP BY date(endDate)
			) as t
			WHERE date IS NOT NULL
			GROUP BY date
		';
		
		$result = $this -> executeNone($query);

		return $result;
	}         
	
	public function stLifespanOfHyip()
	{
		$query = 'TRUNCATE vh_st_lifespan';		
		$this -> executeNone($query);
	
		$query = '
		   INSERT vh_st_lifespan(duration, number, type)
			SELECT duration, COUNT(*) as number, 1 as type
			FROM(
				SELECT IF(enddate IS NULL, DATEDIFF(NOW(), startdate), DATEDIFF(enddate, startdate)) as duration
				FROM vh_hyip
			) t
			GROUP BY duration			
		';		
		$result = $this -> executeNone($query);

		$query = '
		   INSERT vh_st_lifespan(duration, number, type)
			SELECT duration, COUNT(*) as number, 2 as type
			FROM(
				SELECT IF(enddate IS NULL, DATEDIFF(NOW(), startdate), DATEDIFF(enddate, startdate)) as duration
				FROM vh_hyip
				WHERE status <> "Scam" AND status <> "Invested"
			) t
			GROUP BY duration			
		';		
		$result = $this -> executeNone($query);
		
		$query = '
		   INSERT vh_st_lifespan(duration, number, type)
			SELECT duration, COUNT(*) as number, 3 as type
			FROM(
				SELECT IF(enddate IS NULL, DATEDIFF(NOW(), startdate), DATEDIFF(enddate, startdate)) as duration
				FROM vh_hyip
				WHERE status = "Scam" OR status = "Invested"
			) t
			GROUP BY duration			
		';		
		$result = $this -> executeNone($query);
		
		return true;
	}        
	
	public function getSiteNumberDaily()
	{		
		$query = 'SELECT t.date, t.newCount, t.scamCount
		   FROM vh_st_life_daily t
                   ORDER BY t.date desc
                ';
		
		$result = $this -> executeAll($query, array(), false);

		return $result;
	}  

	public function getLifespan()
	{		
		$query = '
		   SELECT *
		   FROM vh_st_lifespan t
                   ORDER BY
		';
		
		$result = $this -> executeAll($query);

		return $result;
	}    	
}